﻿Imports Microsoft.SqlServer.Management.Smo
Imports System.Collections.Specialized

Public Class Utility

#Region "Declaration"
    Private m_ServerName As String
    Private m_DBUser As String
    Private m_DBPassword As String
    Private m_Server As New Server
    Private ReadOnly m_WindowsAuthentication As Boolean = True
#End Region

#Region "Constructor"

    Public Sub New(ByVal serverName As String, ByVal dbUser As String, ByVal dbPassword As String, Optional ByVal isWindowsAuthentication As Boolean = True)
        m_ServerName = serverName
        m_DBUser = dbUser
        m_DBPassword = dbPassword
        m_WindowsAuthentication = isWindowsAuthentication
        ConnectServer()
    End Sub

    Public Sub New()

    End Sub

#End Region

#Region "Property"

    Public Property ServerName() As String
        Get
            Return m_ServerName
        End Get
        Set(ByVal value As String)
            m_ServerName = value
        End Set
    End Property

    Public Property DBUser() As String
        Get
            Return m_DBUser
        End Get
        Set(ByVal value As String)
            m_DBUser = value
        End Set
    End Property

    Public Property DBPassword() As String
        Get
            Return m_DBPassword
        End Get
        Set(ByVal value As String)
            m_DBPassword = value
        End Set
    End Property

#End Region

#Region "Method"

    Public Sub ConvertDatabase(ByVal databaseName As String)
        'Dim sfileName As String = My.Application.Info.DirectoryPath & "\" '& My.Resources.convertScript
        'Dim ds As New DataSet
        'Dim sr As New StreamReader(sfileName)
        'Dim sConten As String = sr.ReadToEnd()
        'm_Server.Databases(databaseName).ExecuteNonQuery(sConten)
    End Sub

    Public Sub Runscript(ByVal fileName As String, ByVal databaseName As String)
        'Dim db As Database = m_Server.Databases(databaseName)
        'Dim ds As New DataSet
        'Dim sr As New StreamReader(fileName)
        'Dim sContent As String = sr.ReadToEnd()
        'm_Server.Databases(databaseName).ExecuteNonQuery(sContent)
    End Sub

    Public Function LoadDatabaseList() As DataTable
        Dim dt As New DataTable
        'Dim oColumn As New DataColumn
        'Dim oRow As DataRow
        'With oColumn
        '    .ColumnName = "Name"
        '    .DataType = Type.GetType("System.String")
        '    dt.Columns.Add(oColumn)
        'End With
        'oColumn = New DataColumn
        'With oColumn
        '    .ColumnName = "PrimaryFilePath"
        '    .DataType = Type.GetType("System.String")
        '    dt.Columns.Add(oColumn)
        'End With

        'If Not m_Server Is Nothing Then
        '    For idb As Integer = 0 To m_Server.Databases.Count - 1
        '        Select Case m_Server.Databases.Item(idb).Name.ToUpper
        '            Case "MASTER", "MODEL", "MSDB", "TEMPDB", "PUBS"
        '            Case Else
        '                oRow = dt.NewRow
        '                oRow(0) = m_Server.Databases.Item(idb).Name
        '                oRow(1) = m_Server.Databases.Item(idb).PrimaryFilePath

        '                dt.Rows.Add(oRow)
        '        End Select
        '    Next
        'End If
        Return dt
    End Function

    Public Function LoadBackupFile(ByVal fileName As String) As DataTable
        Dim dt As New DataTable
        Dim dtResult As New DataTable
        Dim oColumn As New DataColumn
        Dim oRow As DataRow
        Dim oRestore As New Restore

        With oColumn
            .ColumnName = "Name"
            .DataType = System.Type.GetType("System.String")
            dt.Columns.Add(oColumn)
        End With
        oColumn = New DataColumn
        With oColumn
            .ColumnName = "Date"
            .DataType = Type.GetType("System.String")
            dt.Columns.Add(oColumn)
        End With
        oColumn = New DataColumn
        With oColumn
            .ColumnName = "FileNumber"
            .DataType = Type.GetType("System.Int32")
            dt.Columns.Add(oColumn)
        End With

        With oRestore
            .Devices.AddDevice(fileName, DeviceType.File)
            dtResult = .ReadBackupHeader(m_Server)
            For Each dr As DataRow In dtResult.Rows
                oRow = dt.NewRow
                oRow("Name") = dr("DatabaseName")
                oRow("Date") = dr("BackupFinishDate")
                oRow("FileNumber") = dr("Position")
                dt.Rows.Add(oRow)
            Next

        End With

        Return dt
    End Function

    Public Shared Function BackupDatabase(ByVal _server As Server, ByVal databaseName As String, ByVal fileName As String) As Boolean
        Dim oBackup As New Backup
        Dim bResult As Boolean
        With oBackup
            .Database = databaseName
            .Action = BackupActionType.Database
            .Devices.AddDevice(fileName, DeviceType.File)
            .Initialize = True
            .Checksum = True
            .ContinueAfterError = True
            .Incremental = False

            .LogTruncation = BackupTruncateLogType.Truncate
            .BackupSetName = databaseName
            .BackupSetDescription = databaseName & " Database backup"
            .SqlBackup(_server)
        End With
        bResult = True
        Return bResult
    End Function

    Public Shared Function RestoreDatabase(ByVal _server As Server, ByVal databaseName As String, ByVal Path As String, ByVal fileName As String) As Boolean
        Dim oRestore As New Restore
        Dim bResult As Boolean
        Dim dt As DataTable
        With oRestore
            .Database = databaseName
            .Action = RestoreActionType.Database
            .ReplaceDatabase = True
            .Devices.AddDevice(fileName, DeviceType.File)
            '.FileNumber = fileNumber
            .PercentCompleteNotification = 10
            dt = .ReadFileList(_server)
            Dim dr() As DataRow = dt.Select()
            Dim dbfile As String = dr(0).Item("LogicalName")
            Dim logfile As String = dr(1).Item("LogicalName")
            Dim snewdbfileName As String = Path & "\" & databaseName & ".smd"
            Dim snewlogfileName As String = Path & "\" & databaseName & "_log.sld"
            If Path <> "" Then
                .RelocateFiles.Add(New RelocateFile(dbfile, snewdbfileName))
                .RelocateFiles.Add(New RelocateFile(logfile, snewlogfileName))
            End If
            .SqlRestore(_server)
        End With
        bResult = True
        Return bResult
    End Function

    Public Function CheckExistDatabase(ByVal dbName As String) As Boolean
        Dim bExist As Boolean = False
        Dim oResult As New DataSet
        Dim str As String = "SELECT * FROM master.dbo.sysdatabases WHERE name ='" & dbName & "'"
        oResult = m_Server.ConnectionContext.ExecuteWithResults(str)
        If oResult.Tables(0).Rows.Count > 0 Then
            bExist = True
        End If
        Return bExist
    End Function

    Public Shared Function DetachDatabase(ByVal _server As Server, ByVal dbName As String) As Boolean
        Dim bExist As Boolean
        Try
            _server.DetachDatabase(dbName, False)
            bExist = True
        Catch ex As Exception
            bExist = False
        End Try
        Return bExist
    End Function

    Public Function DeleteDatabase(ByVal dbName As String) As Boolean
        Dim bExist As Boolean = False
        Try
            m_Server.KillAllProcesses(dbName)
            m_Server.KillDatabase(dbName)
            bExist = True
        Catch ex As Exception
            bExist = False
        End Try
        Return bExist
    End Function

    Public Function AttachDatabase(ByVal dbName As String, ByVal fileName As String) As Boolean
        Dim bExist As Boolean = False
        Try
            Dim ofile As New StringCollection
            ofile.Add(fileName)
            m_Server.AttachDatabase(dbName, ofile)
            bExist = True
        Catch ex As Exception
            bExist = False
        End Try
        Return bExist
    End Function

    Public Overloads Sub ConnectServer()
        m_Server = New Server
        With m_Server.ConnectionContext

            If m_WindowsAuthentication Then
                .LoginSecure = True
            Else
                .LoginSecure = False
                .Login = m_DBUser
                .Password = m_DBPassword
            End If
            .ServerInstance = m_ServerName
            .Connect()
        End With
    End Sub

    Public Overloads Shared Function ConnectServer(ByVal _instance As String, ByVal _username As String, ByVal _password As String, ByVal _isSecure As Boolean) As Server
        Dim _server As New Server

        With _server.ConnectionContext
            If _isSecure Then
                .LoginSecure = True
            Else
                .LoginSecure = False
                .Login = _username
                .Password = _password
            End If
            .ServerInstance = _instance
            .Connect()
        End With

        Return _server
    End Function

    Public Function ListServer() As DataTable
        Dim dt As DataTable = SmoApplication.EnumAvailableSqlServers()
        Return dt
    End Function

#End Region

End Class